/**
 * 
 */
package fr.greta.centreFormation.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


import fr.greta.centreFormation.bean.Cursus;
import fr.greta.centreFormation.bean.UniteEnseignement;
import fr.greta.centreFormation.controller.UnCursus;

/**
 * @author Betty Boop
 *
 */
public class CursusHome {

	public static int insertCursus(Cursus unCursus, int idDomaine){
		
		int idCursus = 0;
		String intituleCursus= unCursus.getIntituleCursus();
		String descriptionCursus = unCursus.getDescriptionCursus();
		String codeRomeCursus  = unCursus.getCodeRomeCursus();
		String sql = "INSERT INTO cursus(intitule_cursus, description_cursus, code_rome_cursus, id_domaine) VALUES (?,?,?,?)";

		try {
			
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); 
			
			stmt.setString(1, intituleCursus);
			stmt.setString(2, descriptionCursus);
			stmt.setString(3, codeRomeCursus);
			stmt.setInt(4, idDomaine);
			
			stmt.executeUpdate();

			ResultSet result = stmt.getGeneratedKeys();
			result.next();
			idCursus= result.getInt(1);
			
			String insertUniteEnseignementCursus = "INSERT INTO unite_enseignement_cursus (id_unite_enseignement, id_cursus) VALUES (?,?)";
			PreparedStatement stmtUniteCursus = connection.prepareStatement(insertUniteEnseignementCursus);
			
			for(UniteEnseignement ue : unCursus.getCollectiondeUE()){
				stmtUniteCursus.setInt(1, ue.getIdUniteEnseignement());
				stmtUniteCursus.setInt(2, idCursus);
				stmtUniteCursus.executeUpdate();

			}
			
			return idCursus;

		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}

	}

	public static boolean deleteCursus(Cursus unCursus){
		int idCursus = unCursus.getIdCursus();
		String sql ="DELETE FROM cursus WHERE id_cursus ="+idCursus;

		try {
			
			Connection connection = Bdd.getConnection();
			
			String deletUeCursus = "DELETE FROM unite_enseignement_cursus WHERE id_cursus = ?";
			PreparedStatement stmt2 = connection.prepareStatement(deletUeCursus);
			
			stmt2.setInt(1, idCursus);
			
			PreparedStatement stmt = connection.prepareStatement(sql);

			stmt.executeUpdate();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;}
	}

	public static boolean updateCursus(Cursus unCursus, int idDomaine) throws SQLException{
		int idCursus =unCursus.getIdCursus();
		String intituleCursus= unCursus.getIntituleCursus();
		String descriptionCursus= unCursus.getDescriptionCursus();
		String codeRomeCursus= unCursus.getCodeRomeCursus();
		String sql ="UPDATE cursus SET intitule_cursus = ?, description_cursus=?, code_rome_cursus=?, id_domaine=? WHERE id_cursus=?";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);

			stmt.setString(1, intituleCursus);
			stmt.setString(2, descriptionCursus);
			stmt.setString(3, codeRomeCursus);
			stmt.setInt(4, idDomaine);
			stmt.setInt(5, idCursus);

			stmt.executeUpdate();
			
			String deletUeCursus = "DELETE FROM unite_enseignement_cursus WHERE id_cursus = ?";

			PreparedStatement stmt2 = connection.prepareStatement(deletUeCursus);
			
			stmt2.setInt(1, idCursus);
			stmt2.executeUpdate();
			
			String insertUniteEnseignementCursus = "INSERT INTO unite_enseignement_cursus (id_unite_enseignement, id_cursus) VALUES (?,?)";
			PreparedStatement stmtUniteCursus = connection.prepareStatement(insertUniteEnseignementCursus);
			
			for(UniteEnseignement ue : unCursus.getCollectiondeUE()){
				stmtUniteCursus.setInt(1, ue.getIdUniteEnseignement());
				stmtUniteCursus.setInt(2, idCursus);
				stmtUniteCursus.executeUpdate();

			}
			
			return true;

		} catch (SQLException e) {
			e.getStackTrace();
			throw e;
		}

	}

	public static ArrayList<Cursus> selectAllCursus() throws SQLException{
		ArrayList<Cursus> allCursus = new ArrayList<Cursus>();
		String sql = "SELECT * FROM cursus";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt= connection.prepareStatement(sql);

			ResultSet result = stmt.executeQuery();

			while(result.next()){
				
				ArrayList<UniteEnseignement> collectiondeUE = UniteEnseignementHome.selectAllUniteEnseignementByIdCursus(result.getInt(1));
				
				Cursus unCursus = new Cursus();
				unCursus.setIdCursus(result.getInt(1));
				unCursus.setIntituleCursus(result.getString(2));
				unCursus.setDescriptionCursus(result.getString(3));
				unCursus.setCodeRomeCursus(result.getString(4));
				unCursus.setIdDomaine(result.getInt(5));
				unCursus.setCollectiondeUE(collectiondeUE);

				allCursus.add(unCursus);
			}
			return allCursus;

		} catch (SQLException e) {
			e.getStackTrace();
			throw e;		}
	}

	public static Cursus selectUnCursusById(int idCursus){
		ArrayList<Cursus> allCursus = new ArrayList<Cursus>();
		String sql = "SELECT * FROM cursus WHERE id_cursus ="+idCursus;

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);

			ResultSet result =stmt.executeQuery();

			result.next();
			
			ArrayList<UniteEnseignement> collectiondeUE = UniteEnseignementHome.selectAllUniteEnseignementByIdCursus(result.getInt(1));

			Cursus cursus = new Cursus();
			cursus.setIdCursus(result.getInt(1));
			cursus.setIntituleCursus(result.getString(2));
			cursus.setDescriptionCursus(result.getString(3));
			cursus.setCodeRomeCursus(result.getString(4));
			cursus.setIdDomaine(result.getInt(5));
			cursus.setCollectiondeUE(collectiondeUE);
			return cursus;

		} catch (Exception e) {
			e.getMessage();
			return null;
		}
	}


}
